{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Ruth Cadbury. Show the total amount payable by guest Ruth Cadbury for her room bookings. You should JOIN to the rate table using room_type_requested and occupants.\n",
    "\n",
    "```\n",
    "+--------------------+\n",
    "| SUM(nights*amount) |\n",
    "+--------------------+\n",
    "|             552.00 |\n",
    "+--------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Including Extras. Calculate the total bill for booking 5346 including extras.\n",
    "\n",
    "```\n",
    "+-------------+\n",
    "| SUM(amount) |\n",
    "+-------------+\n",
    "|      118.56 |\n",
    "+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Edinburgh Residents. For every guest who has the word “Edinburgh” in their address show the total number of nights booked. Be sure to include 0 for those guests who have never had a booking. Show last name, first name, address and number of nights. Order by last name then first name.\n",
    "\n",
    "```\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| last_name | first_name | address                   | nights |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| Brock     | Deidre     | Edinburgh North and Leith |      0 |\n",
    "| Cherry    | Joanna     | Edinburgh South West      |      0 |\n",
    "| Murray    | Ian        | Edinburgh South           |     13 |\n",
    "| Sheppard  | Tommy      | Edinburgh East            |      0 |\n",
    "| Thomson   | Michelle   | Edinburgh West            |      3 |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "How busy are we? For each day of the week beginning 2016-11-25 show the number of bookings starting that day. Be sure to show all the days of the week in the correct order.\n",
    "\n",
    "```\n",
    "+------------+----------+\n",
    "| i          | arrivals |\n",
    "+------------+----------+\n",
    "| 2016-11-25 |        7 |\n",
    "| 2016-11-26 |        8 |\n",
    "| 2016-11-27 |       12 |\n",
    "| 2016-11-28 |        7 |\n",
    "| 2016-11-29 |       13 |\n",
    "| 2016-11-30 |        6 |\n",
    "| 2016-12-01 |        7 |\n",
    "+------------+----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "How many guests? Show the number of guests in the hotel on the night of 2016-11-21. Include all occupants who checked in that day but not those who checked out.\n",
    "\n",
    "```\n",
    "+----------------+\n",
    "| SUM(occupants) |\n",
    "+----------------+\n",
    "|             39 |\n",
    "+----------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
